﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
using Entity;
using Common;

namespace DataAccess
{
    public class BranchDA
    {
        //SqlDataAdapter DAdapter;
        //tao phuong thuc them moi de goi o proc trong database
        public static List<Branch> generateBranch() {
            List<Branch> Branches = null;
            using (SqlConnection conn = ConnectData.getConnection())
            {
                try
                {
                    conn.Open();
                    SqlCommand cm = new SqlCommand("Get_Branches",conn);
                    cm.CommandType = CommandType.StoredProcedure;
                    SqlDataAdapter da = new SqlDataAdapter(cm);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    Branches = LibConvert.ConvertTo<Branch>(ds.Tables[0]);
                    //SqlDataReader dr = cm.ExecuteReader();
                    //Branches = new List<Branch>();
                    ////Branches = (List<Branch>)dr.Cast<List<Branch>>();
                    //while (dr.Read())
                    //{
                    //    Branch br = new Branch { BranchID = dr.GetInt32(0), BranchName = dr.GetString(1) };
                    //    Branches.Add(br);
                    //}
                    conn.Close();
                }
                catch (Exception ) {
                    MessageBox.Show("error");
                }
            }
            return Branches;
        }

        public bool CreateNew(Branch br)
        {
            //tao ket noi
            using (SqlConnection conn = ConnectData.getConnection()) {
                try
                {
                    //mo connection
                    conn.Open();
                    //tao mot sqlcommand
                    SqlCommand cm = new SqlCommand("spInsert_Branch", conn);
                    //khai bao kiem su dung trong command
                    cm.CommandType = CommandType.StoredProcedure;
                    //add cac du lieu vao trong cm
                    cm.Parameters.Add("@BranchName", SqlDbType.NVarChar);
                    cm.Parameters["@BranchName"].Value = br.BranchName;
                    //thuc thi proc
                    cm.ExecuteNonQuery();
                    //dong connection
                    conn.Close();
                    return true;
                }
                catch (Exception)
                {
                    return false;
                }
            }
        }
        //method get class by branch
        public static List<Classs> GetClassByBranch(int branchID)
        {
            List<Classs> lstBranch = null;//initial List to store list of branch and assign default value
            using(SqlConnection connect = ConnectData.getConnection())
            {
                try
                {
                    //open connection
                    connect.Open();
                    //initial command
                    SqlCommand cmd = connect.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;//set type of Command is called store procedure
                    cmd.CommandText = "spGetClassByBranch";//set name of store procedure
                    cmd.Parameters.Add("@branchID", SqlDbType.Int).Value = branchID;//set parameter of store procedure
                    //initial SqlDataReader to execute
                    SqlDataReader sqlReader = cmd.ExecuteReader();
                    //assign new value for list of branch
                    lstBranch = new List<Classs>();
                    while (sqlReader.Read())
                    {
                        Classs cls = new Classs
                        {
                            ClassID = Convert.ToInt32(sqlReader["ClassID"]),
                            ClassName = Convert.ToString(sqlReader["ClassName"]),
                            FacultyID = Convert.ToInt32(sqlReader["FacultyID"]),
                            BranchID = Convert.ToInt32(sqlReader["BranchID"]),
                            TimeSlot = Convert.ToString(sqlReader["TimeSlot"]),
                            StartDay = Convert.ToDateTime(sqlReader["StartDay"]),
                            EndDay = Convert.ToDateTime(sqlReader["EndDay"]),
                            ClassStatus = Convert.ToString(sqlReader["ClassStatus"])
                        };
                        lstBranch.Add(cls);
                    }
                }
                catch (Exception ex)
                {

                    MessageBox.Show(ex.Message);
                }
            }
            return lstBranch;
        }
        public void update_Branch(Branch objBr)
        {
            using (SqlConnection conn = ConnectData.getConnection())
            {
                try
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "spUpdate_Branch";
                    cmd.Parameters.AddWithValue("@BranchName",objBr.BranchName);
                    cmd.Parameters.AddWithValue("@BranchID", objBr.BranchID);
                    cmd.ExecuteNonQuery();
                }
                catch (Exception)
                {
                    MessageBox.Show("Update Error !");
                }
            }
        }
        public void DeleteBranch(Int32 ID)
        {
            using (SqlConnection conn = ConnectData.getConnection())
            {
                try
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "Delete From tblUser Where MaUser = " + ID;
                    cmd.ExecuteNonQuery();
                }
                catch (Exception)
                {
                    MessageBox.Show("Delete Error !");
                }
            }
            
        }
    }
  
}
